"""
Case Type   : DBlink功能
Case Name   : dblink功能验证
Create At   : 2024/4/28
Owner       : peilinqian
Description :
    PS：用例涉及前置操作及切库操作，结合py脚本实现自动化
    1. 创建数据库及库下创建dblink扩展;
    2. 目标库进行数据库及用户建立;
    3. 目标库进行pg_hba用户白名单配置;
    4. sql脚本目标库信息变更配置;
    5. 目标库进行数据预置;
    6. 执行sql脚本;
    7. 比对执行结果;
Expect      :
    1. 创建数据库及库下创建dblink扩展; expect:成功
    2. 目标库进行数据库及用户建立; expect:成功
    3. 目标库进行pg_hba用户白名单配置; expect:成功
    4. sql脚本目标库信息变更配置; expect:成功
    5. 目标库进行数据预置; expect:执行成功
    6. 执行sql脚本; expect:执行成功
    7. 比对执行结果; expect:执行结果正确
History     :
    Modify by peilinqian 2024-6-14：优化描述信息；
    Modify by peilinqian 2024-7-10：远程节点数不同，导致执行结果比对不匹配，优化用例；
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

com = Common()


@unittest.skipIf(not com.check_node_exists('remote1_PrimaryDbuser'),
                 '无远程主机环境不执行')
class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.remote_db = Node(node='remote1_PrimaryDbuser')
        self.remote_sh = CommonSH('remote1_PrimaryDbuser')
        self.constant = Constant()
        self.u_sys = 'u_dblink_sys0011'
        self.u_com = 'u_dblink_com0011'
        self.db_name_1 = 'db_dblink_case0011_1'
        self.db_name_2 = 'db_dblink_case0011_2'
        self.tb_name = 't_dblink_case0011'
        self.pre_sql_name = 'dblink_remote_pre.sql'
        self.sql_name = 'dblink_case0011.sql'
        self.expect_name = 'dblink_case0011.out'
        self.result_name = 'dblink_case0011_result.out'
        self.source_dir = os.path.join(macro.SCRIPTS_PATH, "dblink")
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "dblink")
        self.target_sql_file = os.path.join(self.target_path, self.sql_name)
        self.target_expect_file = os.path.join(self.target_path,
                                               self.expect_name)
        self.target_pre_sql_file = os.path.join("dblink", self.pre_sql_name)
        self.log.info(self.target_pre_sql_file)

        self.log.info('----远程复制sql及expec文件，并修改权限----')
        com.scp_file(self.pri_root,
                     f"{self.sql_name}", self.target_path,
                     source_dir=self.source_dir)
        com.scp_file(self.pri_root,
                     f"{self.expect_name}", self.target_path,
                     source_dir=self.source_dir)
        com.scp_file(self.remote_db,
                     f"{self.pre_sql_name}", "dblink",
                     source_dir=self.source_dir)
        chmod_cmd = f"chmod 777 -R {self.target_path};" \
                    f"ls -al {self.target_path}"
        self.log.info(chmod_cmd)
        chmod_result = self.pri_root.sh(chmod_cmd).result()
        self.log.info(chmod_result)

    def test_main(self):
        step_txt = '----step1:创建数据库及库下创建dblink扩展; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name_1};" \
                 f"create database {self.db_name_1};"
        db_result = self.pri_sh.execut_db_sql(db_sql)
        self.log.info(db_result)
        create_sql = f"create extension dblink;"
        create_result = self.pri_sh.execut_db_sql(create_sql,
                                                  dbname=self.db_name_1)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_result,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.create_extension_success, create_result,
                      "执行失败" + step_txt)

        step_txt = '----step2: 目标库进行数据库及用户建立; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name_1};" \
                 f"drop database if exists {self.db_name_2};" \
                 f"create database {self.db_name_1};" \
                 f"create database {self.db_name_2};"
        msg1 = self.remote_sh.execut_db_sql(db_sql,
                                            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg1)
        user_sql = f"drop user if exists {self.u_sys},{self.u_com} cascade;" \
                   f"create user {self.u_sys} sysadmin " \
                   f"password '{macro.PASSWD_INITIAL}';" \
                   f"create user {self.u_com} password " \
                   f"'{macro.PASSWD_INITIAL}';"
        msg2 = self.remote_sh.execut_db_sql(user_sql,
                                            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg2)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1,
                      "执行失败" + step_txt)
        assert_flag = msg2.count(self.constant.CREATE_ROLE_SUCCESS_MSG)
        self.assertEqual(assert_flag, 2, "执行失败" + step_txt)

        step_txt = '----step3: 目标库进行pg_hba用户白名单配置; expect:成功----'
        self.log.info(step_txt)
        self.log.info('----dblink端pg_hba配置----')
        guc_res = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_1}  all '
            f'{self.pri_dbuser.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, "执行失败" + step_txt)
        guc_res = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_2}  all '
            f'{self.pri_dbuser.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, "执行失败" + step_txt)

        step_txt = '----step4: sql脚本目标库信息变更配置; expect:成功----'
        self.log.info(step_txt)
        target_info = ['remote_ip', 'remote_db1', 'remote_db2', 'remote_port',
                       'remote_user_sys', 'remote_user_com', 'remote_passwd',
                       'dblink_ip', 'remote_tb']
        reset_info = [self.remote_db.db_host, self.db_name_1, self.db_name_2,
                      self.remote_db.db_port, self.u_sys, self.u_com,
                      macro.PASSWD_INITIAL, self.pri_dbuser.db_host,
                      self.tb_name]
        for item_index, item in enumerate(target_info):
            msg = com.replace_file_content_by_sed(self.pri_dbuser,
                                                  self.target_sql_file,
                                                  item,
                                                  reset_info[item_index])
            self.log.info(msg)
            self.assertEqual('', msg, "执行失败" + step_txt)
            msg = com.replace_file_content_by_sed(self.pri_dbuser,
                                                  self.target_expect_file,
                                                  item,
                                                  reset_info[item_index])
            self.log.info(msg)
            self.assertEqual('', msg, "执行失败" + step_txt)
            msg = com.replace_file_content_by_sed(self.remote_db,
                                                  self.target_pre_sql_file,
                                                  item,
                                                  reset_info[item_index])
            self.log.info(msg)
            self.assertEqual('', msg, "执行失败" + step_txt)

        step_txt = '----step5: 目标库进行数据预置; expect:执行成功----'
        self.log.info(step_txt)
        sql_text = self.pri_dbuser.sh(
            f"cd {self.target_path};cat {self.pre_sql_name}").result()
        self.log.info(sql_text)
        shell = f'source {macro.DB_ENV_PATH_REMOTE1};' \
                f'gsql -d {self.db_name_1} -p {self.remote_db.db_port} ' \
                f'-f {self.target_pre_sql_file}'
        self.log.info(shell)
        result = self.remote_db.sh(shell).result()
        self.log.info(result)
        self.assertNotIn('err', result.lower(), "执行失败" + step_txt)

        step_txt = '----step6: 执行sql脚本; expect:执行成功----'
        self.log.info(step_txt)
        shell = f'cd {self.target_path};' \
                f'source {macro.DB_ENV_PATH}; ' \
                f'gsql -d {self.db_name_1} ' \
                f'-p {self.pri_dbuser.db_port} ' \
                f'-a < {self.sql_name} > {self.result_name} 2>&1'
        self.log.info(shell)
        result = self.pri_dbuser.sh(shell).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

        step_txt = '----step7:比对执行结果; expect:执行结果正确----'
        self.log.info(step_txt)
        shell = f"cd {self.target_path};" \
                f"sed -i 's/dn_6001.*/*/g' {self.expect_name};" \
                f"sed -i 's/dn_6001.*/*/g' {self.result_name}"
        self.log.info(shell)
        result = self.pri_dbuser.sh(shell).result()
        self.log.info(result)
        shell = f'cd {self.target_path};' \
                f'diff {self.expect_name} {self.result_name}'
        self.log.info(shell)
        result = self.pri_dbuser.sh(shell).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step8_txt = '----step8: dblink侧清理数据; expect: 清理成功----'
        self.log.info(step8_txt)
        drop_db_sql = f"drop database if exists {self.db_name_1};"
        drop_db_msg1 = self.pri_sh.execut_db_sql(drop_db_sql)
        self.log.info(drop_db_msg1)

        step9_txt = '----step9: 目标库进行pg_hba取消用户白名单配置; expect: 成功----'
        self.log.info(step9_txt)
        guc_res1 = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_1}  all '
            f'{self.pri_dbuser.db_host}/32',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res1)
        guc_res2 = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_2}  all '
            f'{self.pri_dbuser.db_host}/32',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res2)

        step10_txt = '----step10: 目标库进行数据库及用户删除; expect:成功----'
        self.log.info(step10_txt)
        drop_db_sql = f"clean connection to all force for database " \
                      f"{self.db_name_1};" \
                      f"clean connection to all force for database " \
                      f"{self.db_name_2};" \
                      f"drop database if exists {self.db_name_1};" \
                      f"drop database if exists {self.db_name_2};"
        drop_db_msg2 = self.remote_sh.execut_db_sql(
            drop_db_sql, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_db_msg2)
        drop_user_sql = f"drop user if exists " \
                        f"{self.u_sys},{self.u_com} cascade;"
        drop_user_msg = self.remote_sh.execut_db_sql(
            drop_user_sql, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_user_msg)
        file_rm_cmd = f'rm -rf dblink;' \
                      f'if [ -d dblink ]; ' \
                      f'then echo "exists"; else echo "not exists"; fi'
        self.log.info(file_rm_cmd)
        file_rm_result = self.remote_db.sh(file_rm_cmd).result()
        self.log.info(file_rm_result)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db_msg1,
                      "执行失败" + step8_txt)
        self.assertTrue(guc_res1, "执行失败" + step9_txt)
        self.assertTrue(guc_res2, "执行失败" + step9_txt)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db_msg2,
                      "执行失败" + step10_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, drop_user_msg,
                      "执行失败" + step10_txt)
        self.assertEqual('not exists', file_rm_result, "执行失败" + step10_txt)
